--
-- PostgreSQL database dump
--

-- Started on 2008-07-25 23:16:38 CEST

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 1650 (class 1262 OID 32786)
-- Name: test_mdag2; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE test_mdag2 WITH TEMPLATE = template0 ENCODING = 'UTF8';


ALTER DATABASE test_mdag2 OWNER TO postgres;

\connect test_mdag2

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 274 (class 2612 OID 32789)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 19 (class 1255 OID 32790)
-- Dependencies: 5 274
-- Name: block(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION block() RETURNS "trigger"
    AS $$
DECLARE 
BEGIN
RETURN NULL;
END;$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.block() OWNER TO postgres;

--
-- TOC entry 20 (class 1255 OID 32791)
-- Dependencies: 5 274
-- Name: cache_newedge(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION cache_newedge() RETURNS "trigger"
    AS $$
DECLARE 
r RECORD;
ri RECORD ;
rj RECORD ;
BEGIN
IF 0 = (SELECT COUNT(*) FROM optim WHERE optim.edge_id = NEW.edge_id)
  THEN
    IF 0 = (SELECT COUNT(*) FROM optim2 WHERE optim2.node_id = NEW.node_id1 AND optim2.graph_id = NEW.graph_id)
      THEN
      FOR r IN (SELECT * FROM optim2 WHERE optim2.node_id = NEW.node_id2 AND optim2.graph_id = NEW.graph_id )
      LOOP
      
        UPDATE optim SET optim_right = optim_right + 2
          WHERE optim.graph_id = NEW.graph_id AND optim_right > r.optim_left;
        UPDATE optim SET optim_left = optim_left+ 2 
          WHERE optim.graph_id = NEW.graph_id AND optim_left > r.optim_left;
        INSERT INTO optim(edge_id, optim_left, optim_right, graph_id) VALUES (NEW.edge_id, r.optim_left +1  , r. optim_left + 2, NEW.graph_id);     
      END LOOP;
     ELSE
       SELECT INTO ri * FROM optim2 WHERE optim2.node_id = NEW.node_id1 AND optim2.graph_id = NEW.graph_id LIMIT 1;
       FOR r IN (SELECT * FROM optim2 WHERE optim2.node_id = NEW.node_id2) LOOP
         UPDATE optim SET optim_right = optim_right + ri.optim_right - ri.optim_left + 1 WHERE  optim.graph_id = NEW.graph_id AND optim_right > r.optim_left;
         UPDATE optim SET optim_left = optim_left + ri.optim_right - ri.optim_left + 1  WHERE optim.graph_id = NEW.graph_id AND optim_left > r.optim_left;
	 
	     INSERT INTO optim(edge_id, optim_left, optim_right, graph_id) VALUES(NEW.edge_id, r.optim_left+1, r.optim_left + 1 +  ri.optim_right - ri.optim_left, NEW.graph_id);	
	  
	  SELECT INTO rj * FROM optim2 WHERE optim2.optim_id = ri.optim_id;
        INSERT INTO optim 
          SELECT 
          nextval('optim_optim_id_seq'::regclass) As optim_id,
          optim.edge_id As edge_id, 
          optim.optim_left + r.optim_left - rj.optim_left +1 AS optim_left, 
          optim.optim_right + r.optim_left - rj.optim_left +1 AS optim_right,
          NEW.graph_id As graph_id
          FROM optim
          WHERE optim.optim_left > rj.optim_left AND optim.optim_right < rj.optim_right AND optim.graph_id = NEW.graph_id;
      
       END LOOP;
		

    END IF;


END IF;
RETURN NEW;
END;$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.cache_newedge() OWNER TO postgres;

--
-- TOC entry 21 (class 1255 OID 32792)
-- Dependencies: 5 274
-- Name: dag_check(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dag_check() RETURNS "trigger"
    AS $$
DECLARE
i INTEGER;

BEGIN
IF 0 < 
(SELECT COUNT(*) FROM optim3 WHERE optim3.node_id1 = NEW.node_id2 AND optim3.node_id2 = NEW.node_id1 AND optim3.graph_id = NEW.graph_id)
  THEN
    RETURN NULL;
  ELSE
     SELECT INTO i node_id FROM graph WHERE graph.graph_id  = NEW.graph_id;
     
     IF  (0 < (SELECT COUNT(*) FROM optim3 WHERE optim3.node_id1 = NEW.node_id2 AND optim3.node_id2 = i AND optim3.graph_id = NEW.graph_id) )
   OR (NEW.node_id2 = i)
  THEN
      RETURN NEW;
  ELSE
    RETURN NULL;	 
  END IF;
END IF;

END;$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.dag_check() OWNER TO postgres;

--
-- TOC entry 22 (class 1255 OID 32793)
-- Dependencies: 274 5
-- Name: edge_delete(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION edge_delete() RETURNS "trigger"
    AS $$
DECLARE 

r RECORD;
i INTEGER;
BEGIN
    
    
  IF 1 = (SELECT COUNT(*) FROM edge WHERE edge.node_id1 = OLD.node_id1 AND edge.graph_id = OLD.graph_id)
    THEN
      IF 0 < (SELECT COUNT(*) FROM edge WHERE edge.node_id2 = OLD.node_id1 AND edge.graph_id = OLD.graph_id)
        THEN
          RETURN NULL;
    END IF;
  END IF;


    
  SELECT INTO r * FROM optim WHERE optim.edge_id = OLD.edge_id LIMIT 1;
  SELECT INTO i COUNT(*) FROM optim
    WHERE optim.graph_id = OLD.graph_id AND optim.optim_left > r.optim_left 
    AND optim.optim_right < r.optim_right; 

    FOR r IN (SELECT * FROM optim WHERE optim.edge_id = OLD.edge_id) LOOP
      DELETE FROM optim WHERE optim.graph_id = OLD.graph_id AND optim.optim_left > r.optim_left-1 
    AND optim.optim_right < r.optim_right+1;
    
	UPDATE optim SET optim_right = optim_right - i*2 WHERE optim.graph_id = OLD.graph_id AND optim_right > r.optim_left;
    UPDATE optim SET optim_left = optim_left - i*2 WHERE optim.graph_id = OLD.graph_id AND optim_left > r.optim_left;
	 	
      
    END LOOP;
RETURN OLD;
END;$$
    LANGUAGE plpgsql;


ALTER FUNCTION public.edge_delete() OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1279 (class 1259 OID 32794)
-- Dependencies: 5
-- Name: edge; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE edge (
    edge_id integer NOT NULL,
    node_id1 bigint NOT NULL,
    node_id2 bigint NOT NULL,
    graph_id bigint NOT NULL
);


ALTER TABLE public.edge OWNER TO postgres;

--
-- TOC entry 1280 (class 1259 OID 32796)
-- Dependencies: 1279 5
-- Name: edge_edge_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE edge_edge_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.edge_edge_id_seq OWNER TO postgres;

--
-- TOC entry 1653 (class 0 OID 0)
-- Dependencies: 1280
-- Name: edge_edge_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE edge_edge_id_seq OWNED BY edge.edge_id;


--
-- TOC entry 1654 (class 0 OID 0)
-- Dependencies: 1280
-- Name: edge_edge_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('edge_edge_id_seq', 29, true);


--
-- TOC entry 1281 (class 1259 OID 32798)
-- Dependencies: 5
-- Name: graph; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE graph (
    graph_id integer NOT NULL,
    node_id integer NOT NULL
);


ALTER TABLE public.graph OWNER TO postgres;

--
-- TOC entry 1282 (class 1259 OID 32800)
-- Dependencies: 5
-- Name: node; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE node (
    node_id integer NOT NULL
);


ALTER TABLE public.node OWNER TO postgres;

--
-- TOC entry 1283 (class 1259 OID 32802)
-- Dependencies: 1282 5
-- Name: node_node_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE node_node_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.node_node_id_seq OWNER TO postgres;

--
-- TOC entry 1655 (class 0 OID 0)
-- Dependencies: 1283
-- Name: node_node_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE node_node_id_seq OWNED BY node.node_id;


--
-- TOC entry 1656 (class 0 OID 0)
-- Dependencies: 1283
-- Name: node_node_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('node_node_id_seq', 1, false);


--
-- TOC entry 1284 (class 1259 OID 32804)
-- Dependencies: 5
-- Name: optim; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE optim (
    optim_id integer NOT NULL,
    edge_id bigint NOT NULL,
    optim_left bigint NOT NULL,
    optim_right bigint NOT NULL,
    graph_id bigint NOT NULL
);


ALTER TABLE public.optim OWNER TO postgres;

--
-- TOC entry 1285 (class 1259 OID 32806)
-- Dependencies: 1362 5
-- Name: optim2; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW optim2 AS
    SELECT optim.optim_id, edge.graph_id, edge.node_id1 AS node_id, edge.node_id2 AS note_parentid, optim.optim_left, optim.optim_right FROM (optim JOIN edge ON ((optim.edge_id = edge.edge_id))) UNION SELECT NULL::"unknown" AS optim_id, graph.graph_id, graph.node_id, NULL::"unknown" AS note_parentid, 1 AS optim_left, (2 * (SELECT (count(*) + 1) FROM (optim JOIN edge ON ((optim.edge_id = edge.edge_id))) WHERE (edge.graph_id = graph.graph_id))) AS optim_right FROM graph;


ALTER TABLE public.optim2 OWNER TO postgres;

--
-- TOC entry 1286 (class 1259 OID 32810)
-- Dependencies: 1363 5
-- Name: optim3; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW optim3 AS
    SELECT DISTINCT optim2.graph_id, optim2.node_id AS node_id1, t2.node_id AS node_id2 FROM (optim2 JOIN optim2 t2 ON ((t2.graph_id = optim2.graph_id))) WHERE ((t2.optim_left < optim2.optim_left) AND (t2.optim_right > optim2.optim_right)) ORDER BY optim2.node_id, t2.node_id, optim2.graph_id;


ALTER TABLE public.optim3 OWNER TO postgres;

--
-- TOC entry 1287 (class 1259 OID 32813)
-- Dependencies: 5 1284
-- Name: optim_optim_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE optim_optim_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.optim_optim_id_seq OWNER TO postgres;

--
-- TOC entry 1657 (class 0 OID 0)
-- Dependencies: 1287
-- Name: optim_optim_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE optim_optim_id_seq OWNED BY optim.optim_id;


--
-- TOC entry 1658 (class 0 OID 0)
-- Dependencies: 1287
-- Name: optim_optim_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('optim_optim_id_seq', 44, true);


--
-- TOC entry 1620 (class 2604 OID 32815)
-- Dependencies: 1280 1279
-- Name: edge_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE edge ALTER COLUMN edge_id SET DEFAULT nextval('edge_edge_id_seq'::regclass);


--
-- TOC entry 1621 (class 2604 OID 32816)
-- Dependencies: 1283 1282
-- Name: node_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE node ALTER COLUMN node_id SET DEFAULT nextval('node_node_id_seq'::regclass);


--
-- TOC entry 1622 (class 2604 OID 32817)
-- Dependencies: 1287 1284
-- Name: optim_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE optim ALTER COLUMN optim_id SET DEFAULT nextval('optim_optim_id_seq'::regclass);


--
-- TOC entry 1644 (class 0 OID 32794)
-- Dependencies: 1279
-- Data for Name: edge; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY edge (edge_id, node_id1, node_id2, graph_id) FROM stdin;
24	1	0	0
25	2	1	0
26	3	2	0
27	4	3	0
28	5	4	0
29	2	0	0
\.


--
-- TOC entry 1645 (class 0 OID 32798)
-- Dependencies: 1281
-- Data for Name: graph; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY graph (graph_id, node_id) FROM stdin;
0	0
1	1
\.


--
-- TOC entry 1646 (class 0 OID 32800)
-- Dependencies: 1282
-- Data for Name: node; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY node (node_id) FROM stdin;
0
1
2
3
4
5
6
7
8
9
10
\.


--
-- TOC entry 1647 (class 0 OID 32804)
-- Dependencies: 1284
-- Data for Name: optim; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY optim (optim_id, edge_id, optim_left, optim_right, graph_id) FROM stdin;
36	24	10	19	0
37	25	11	18	0
38	26	12	17	0
39	27	13	16	0
40	28	14	15	0
41	29	2	9	0
42	26	3	8	0
43	27	4	7	0
44	28	5	6	0
\.


--
-- TOC entry 1624 (class 2606 OID 32819)
-- Dependencies: 1279 1279 1279
-- Name: edge_node_id1_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY edge
    ADD CONSTRAINT edge_node_id1_key UNIQUE (node_id1, node_id2);


--
-- TOC entry 1630 (class 2606 OID 32821)
-- Dependencies: 1282 1282
-- Name: pk; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY node
    ADD CONSTRAINT pk PRIMARY KEY (node_id);


--
-- TOC entry 1626 (class 2606 OID 32823)
-- Dependencies: 1279 1279
-- Name: pkedge; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY edge
    ADD CONSTRAINT pkedge PRIMARY KEY (edge_id);


--
-- TOC entry 1628 (class 2606 OID 32825)
-- Dependencies: 1281 1281
-- Name: pkgraph; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY graph
    ADD CONSTRAINT pkgraph PRIMARY KEY (graph_id);


--
-- TOC entry 1633 (class 2606 OID 32827)
-- Dependencies: 1284 1284
-- Name: pktreenode; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY optim
    ADD CONSTRAINT pktreenode PRIMARY KEY (optim_id);


--
-- TOC entry 1631 (class 1259 OID 32828)
-- Dependencies: 1284
-- Name: fki_optimf; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX fki_optimf ON optim USING btree (edge_id);


--
-- TOC entry 1639 (class 2620 OID 32829)
-- Dependencies: 20 1279
-- Name: edge_after_insert; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER edge_after_insert
    AFTER INSERT ON edge
    FOR EACH ROW
    EXECUTE PROCEDURE cache_newedge();


--
-- TOC entry 1640 (class 2620 OID 32830)
-- Dependencies: 22 1279
-- Name: edge_bd; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER edge_bd
    BEFORE DELETE ON edge
    FOR EACH ROW
    EXECUTE PROCEDURE edge_delete();


--
-- TOC entry 1641 (class 2620 OID 32831)
-- Dependencies: 21 1279
-- Name: edge_before_insert; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER edge_before_insert
    BEFORE INSERT ON edge
    FOR EACH ROW
    EXECUTE PROCEDURE dag_check();


--
-- TOC entry 1642 (class 2620 OID 32832)
-- Dependencies: 19 1279
-- Name: edge_update; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER edge_update
    BEFORE UPDATE ON edge
    FOR EACH ROW
    EXECUTE PROCEDURE block();


--
-- TOC entry 1643 (class 2620 OID 32833)
-- Dependencies: 19 1281
-- Name: graph_update; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER graph_update
    BEFORE UPDATE ON graph
    FOR EACH ROW
    EXECUTE PROCEDURE block();


--
-- TOC entry 1634 (class 2606 OID 32834)
-- Dependencies: 1282 1629 1279
-- Name: edgef1; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY edge
    ADD CONSTRAINT edgef1 FOREIGN KEY (node_id1) REFERENCES node(node_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1635 (class 2606 OID 32839)
-- Dependencies: 1281 1627 1279
-- Name: edgef2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY edge
    ADD CONSTRAINT edgef2 FOREIGN KEY (graph_id) REFERENCES graph(graph_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1636 (class 2606 OID 32844)
-- Dependencies: 1282 1629 1279
-- Name: edgef3; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY edge
    ADD CONSTRAINT edgef3 FOREIGN KEY (node_id2) REFERENCES node(node_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1637 (class 2606 OID 32849)
-- Dependencies: 1282 1629 1281
-- Name: fkgraphnode; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY graph
    ADD CONSTRAINT fkgraphnode FOREIGN KEY (node_id) REFERENCES node(node_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1638 (class 2606 OID 32854)
-- Dependencies: 1625 1284 1279
-- Name: optimf; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY optim
    ADD CONSTRAINT optimf FOREIGN KEY (edge_id) REFERENCES edge(edge_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1652 (class 0 OID 0)
-- Dependencies: 5
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2008-07-25 23:16:39 CEST

--
-- PostgreSQL database dump complete
--

